/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.HoraBancoDto;
import dto.HoraDto;
import dto.HoraMinutoSegundo;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class AcessoDAO {

    AcessoBancoMySql instanceBanco = AcessoBancoMySql.getInstance();

    public AcessoDAO() {
    }


    public void incluirTitulo(String item){
        String sql = "INSERT INTO base_tv.TB_TITULO (descricao) VALUES (?)";
        try {

            if(!item.equals("")){


            PreparedStatement pst = instanceBanco.startConnection().prepareStatement(sql);

            pst.setString(1, item);

            pst.execute();

            pst.close();
            instanceBanco.destroyConnection();
            }
        } catch (SQLException e) {
            System.err.println("\nError Menssage: " + e.getMessage());
        } catch (Exception ex) {
            System.err.println("\nError Menssage: " + ex.getMessage());
        }
        //destroyConnection();
        try {
            instanceBanco.destroyConnection();
        } catch (Exception e) {
            System.err.println("\nError Menssage: " + e.getMessage());
        }
    }
    
    public void deletarTitulo(String id){
        String sql = "DELETE FROM base_tv.TB_TITULO WHERE ID = ?";
        try {

            if(!id.equals("")){


            PreparedStatement pst = instanceBanco.startConnection().prepareStatement(sql);

            int del = Integer.parseInt(id);

            pst.setInt(1, del);

            pst.execute();

            pst.close();
            instanceBanco.destroyConnection();
            }
        } catch (SQLException e) {
            System.err.println("\nError Menssage: " + e.getMessage());
        } catch (Exception ex) {
            System.err.println("\nError Menssage: " + ex.getMessage());
        }
        //destroyConnection();
        try {
            instanceBanco.destroyConnection();
        } catch (Exception e) {
            System.err.println("\nError Menssage: " + e.getMessage());
        }
    }

    public Map<String, Integer> recuperaRegistrosTituloMostrar() {
        String sql = "SELECT id, descricao FROM base_tv.TB_TITULO";
        Map<String, Integer> retorno = new HashMap<String, Integer>();
        //startConnection();
        try {
            PreparedStatement pst = instanceBanco.startConnection().prepareStatement(sql);

            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                retorno.put(rs.getString("descricao"), rs.getInt("id"));
            }

            rs.close();
            pst.close();
            instanceBanco.destroyConnection();
        } catch (SQLException e) {
            System.err.println("\nError Menssage: " + e.getMessage());
        } catch (Exception ex) {
            System.err.println("\nError Menssage: " + ex.getMessage());
        }
        //destroyConnection();
        try {
            instanceBanco.destroyConnection();
        } catch (Exception e) {
            System.err.println("\nError Menssage: " + e.getMessage());
        }
        return retorno;
    }

    public Map<String, String> recuperaRegistrosTitulo() {
        String sql = "SELECT * FROM base_tv.TB_TITULO";
        Map<String, String> retorno = new HashMap<String, String>();
        //startConnection();
        try {
            PreparedStatement pst = instanceBanco.startConnection().prepareStatement(sql);

            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                retorno.put(rs.getString("DESCRICAO"),rs.getString("ID"));
            }

            rs.close();
            pst.close();
            instanceBanco.destroyConnection();
        } catch (SQLException e) {
            System.err.println("\nError Menssage: " + e.getMessage());
        } catch (Exception ex) {
            System.err.println("\nError Menssage: " + ex.getMessage());
        }
        //destroyConnection();
        try {
            instanceBanco.destroyConnection();
        } catch (Exception e) {
            System.err.println("\nError Menssage: " + e.getMessage());
        }
        return retorno;
    }

    public List<HoraDto> recuperaRegistros() {
        String sql = "SELECT * FROM base_tv.horarios";
        List retorno = new ArrayList();
        //startConnection();
        try {
            PreparedStatement pst = instanceBanco.startConnection().prepareStatement(sql);

            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                //retorno.add(new HoraDto(rs.getInt("id_horarios"), rs.getDate("hora_inical"), rs.getDate("hora_producao"), rs.getDate("hora_comercial")));
            }
            rs.close();
            pst.close();
            instanceBanco.destroyConnection();
        } catch (SQLException e) {
            System.err.println("\nError Menssage: " + e.getMessage());
        } catch (Exception ex) {
            System.err.println("\nError Menssage: " + ex.getMessage());
        }
        //destroyConnection();
        try {
            instanceBanco.destroyConnection();
        } catch (Exception e) {
            System.err.println("\nError Menssage: " + e.getMessage());
        }
        return retorno;
    }

    public List<HoraDto> recuperaRegistroComParametros(String sNome) {
        String sql = "SELECT * FROM TB_PESSOA WHERE NOME like ? ";
        List retorno = new ArrayList();
        //startConnection();
        try {
            PreparedStatement pst = instanceBanco.startConnection().prepareStatement(sql);
            pst.setString(1, "%" + sNome + "%");

            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                //retorno.add(new DTO(rs.getString("NOME"), rs.getString("ENDERECO")));
            }
            rs.close();
            pst.close();
        } catch (SQLException e) {
            System.err.println("\nError Menssage: " + e.getMessage());
        } catch (Exception ex) {
            System.err.println("\nError Menssage: " + ex.getMessage());
        }
        //destroyConnection();
        instanceBanco.destroyConnection();
        return retorno;
    }

    public void gravaBanco(HoraBancoDto conteudo){
        String sql = "INSERT INTO base_tv.HORARIO (TB_TITULO_id, INICIO, FIM, COMERCIAL) VALUES (?, ?, ?, ?)";

        
        Calendar cal_inicio = new GregorianCalendar();
        Calendar cal_fim = new GregorianCalendar();
        Calendar cal_comecial = new GregorianCalendar();
        Calendar cal_tempo = new GregorianCalendar();

        cal_inicio.setTimeInMillis(System.currentTimeMillis());
        cal_fim.setTimeInMillis(System.currentTimeMillis());
        cal_comecial.setTimeInMillis(System.currentTimeMillis());
        cal_tempo.setTimeInMillis(System.currentTimeMillis());

        cal_inicio.set(Calendar.HOUR_OF_DAY, conteudo.getInicio().getHora());
        cal_inicio.set(Calendar.MINUTE, conteudo.getInicio().getMinuto());
        cal_inicio.set(Calendar.SECOND, conteudo.getInicio().getSegundo());


        cal_fim.set(Calendar.HOUR_OF_DAY, conteudo.getFim().getHora());
        cal_fim.set(Calendar.MINUTE, conteudo.getFim().getMinuto());
        cal_fim.set(Calendar.SECOND, conteudo.getFim().getSegundo());

        
        cal_comecial.set(Calendar.HOUR_OF_DAY, conteudo.getComercial().getHora());
        cal_comecial.set(Calendar.MINUTE, conteudo.getComercial().getMinuto());
        cal_comecial.set(Calendar.SECOND, conteudo.getComercial().getSegundo());

        

        try {

            Connection con = AcessoBancoMySql.getInstance().startConnection();
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, conteudo.getTitulo());

            ps.setLong(2, cal_inicio.getTimeInMillis());
            ps.setLong(3, cal_fim.getTimeInMillis());
            ps.setLong(4, cal_comecial.getTimeInMillis());

            ps.execute();

            ps.close();
            con.close();
            AcessoBancoMySql.getInstance().destroyConnection();

        } catch (Exception e){
                System.out.println("Não gravou");
                AcessoBancoMySql.getInstance().destroyConnection();
        }
    }


    public HoraDto ultimoRegistroPorPK() {
        String sql = "SELECT h.ID_HORARIO, t.descricao, h.INICIO, h.FIM, h.COMERCIAL FROM base_tv.HORARIO AS h JOIN base_tv.TB_TITULO AS t ON t.id = h.TB_TITULO_id WHERE ID_HORARIO = (SELECT MAX(ID_HORARIO) FROM base_tv.HORARIO)";
        HoraDto retorno = null;
        int valor;
        //startConnection();
        try {
            PreparedStatement pst = instanceBanco.startConnection().prepareStatement(sql);
            ResultSet rs = pst.executeQuery();

            //while (rs.next()) {
                rs.next();
                valor = rs.getInt("h.ID_HORARIO");
                String titulo = rs.getString("t.descricao");
                long valor2 = rs.getLong("h.INICIO");
                long valor3 = rs.getLong("h.FIM");
                long valor4 = rs.getLong("h.COMERCIAL");
                retorno = new HoraDto(rs.getInt("h.ID_HORARIO"), rs.getString("t.descricao"), rs.getLong("h.INICIO"), rs.getLong("h.FIM"), rs.getLong("h.COMERCIAL"));
            //}

            rs.close();
            pst.close();
        } catch (SQLException e) {
            System.err.println("\nError Menssage: " + e.getMessage());
            instanceBanco.destroyConnection();
            return retorno;
        } catch (Exception ex) {
            System.err.println("\nError Menssage: " + ex.getMessage());
            instanceBanco.destroyConnection();
            return retorno;
        }
        //destroyConnection();
        instanceBanco.destroyConnection();
        return retorno;
    }
}
